package com.cloudcross.ssp.service.impl;

import java.sql.ResultSet;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.adinall.dsp.core.db.DBHelper;
import com.adinall.dsp.core.utils.CheckUtils;
import com.adinall.dsp.core.utils.NumberUtils;
import com.adinall.dsp.core.utils.StringUtils;
import com.cloudcross.ssp.base.dao.GenericIBatisDao;
import com.cloudcross.ssp.common.Config;
import com.cloudcross.ssp.common.consts.FloorPrice;
import com.cloudcross.ssp.common.utils.DateUtil;
import com.cloudcross.ssp.common.utils.PropertiesUtils;
import com.cloudcross.ssp.model.AdsCpmAndCpc;
import com.cloudcross.ssp.service.AdsService;
import com.cloudcross.util.SqlServer;

/**
 * 广告位用Service
 * 
 * @author 高万超
 * @version V1.0.0 $ 2017年6月30日 下午12:06:51
 */
@Service
public class AdsServiceImpl implements AdsService {

    private Logger logger = LogManager.getLogger(getClass());

    @Autowired
    private GenericIBatisDao mapper;

    @Autowired
    private Config config;

    public void syncApp() {

        SqlServer server = new SqlServer(config.getAppHost(), NumberUtils.parseInt(config.getAppPort()), config.getAppUser(), config.getAppPass(),
                config.getAppDbname(), 0);
        logger.info("创建了一个app数据库链接");

        int day = NumberUtils.parseInt(PropertiesUtils.getProperty("/config.properties", "sync.ads.cpm"));

        Calendar c = Calendar.getInstance();
        c.add(Calendar.DAY_OF_YEAR, -day);

        String thatDay = DateUtil.getDate(DateUtil.DateFmts.YYYY_MM_DD, c.getTime());

        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");
        sql.append("    A.ID AS id, ");
        sql.append("    A.ADID AS adid, ");
        sql.append("    IFNULL(A.CPM_PRICE, 0) AS minCpm,     ");
        sql.append("    IFNULL(ROUND(B.REVENUE * 1000 / B.CLICKS, 2), 0) AS cpc, ");
        sql.append("    IFNULL(ROUND(B.REVENUE * 1000 / B.IMPRESSIONS, 2), 0) AS cpm ");
        sql.append("FROM management_ads A ");
        sql.append("LEFT JOIN ( ");
        sql.append("    SELECT ");
        sql.append("        ADID, ");
        sql.append("        SUM(REQUESTS) AS REQUESTS, ");
        sql.append("        SUM(REVENUE) AS REVENUE, ");
        sql.append("        SUM(CLICKS) AS CLICKS, ");
        sql.append("        SUM(IMPRESSIONS) AS IMPRESSIONS ");
        sql.append("    FROM report_daily_channel_data ");
        sql.append("    WHERE DATE >= '" + thatDay + "' ");
        sql.append("    AND REQUESTS > 1000 ");
        sql.append("    GROUP BY ADID ");
        sql.append(") B ON A.ID = B.ADID ");

        String title = "[APP -> ADS]";
        List<AdsCpmAndCpc> data = pull(server, sql.toString(), title);

        List<Map<String, Object>> list = mapper.getList("syncAdsSqlMapper.listApp");

        for (Map<String, Object> map : list) {

            AdsCpmAndCpc tmp = get(data, StringUtils.toString(map.get("adid")));

            if (CheckUtils.isNull(tmp)) {
                continue;
            }

            map.put("cpm", NumberUtils.max(tmp.getCpm(), NumberUtils.parseDouble(map.get("cpm_price")), FloorPrice.CPM));
            map.put("cpc", NumberUtils.max(tmp.getCpc(), FloorPrice.CPC));
            mapper.save("syncAdsSqlMapper.updateApp", map);
        }

        logger.info("cpm、cpc更新成功：" + title);
    }

    public void syncAdx() {

        SqlServer server = new SqlServer(config.getAppHost(), NumberUtils.parseInt(config.getAppPort()), config.getAppUser(), config.getAppPass(),
                config.getAppDbname(), 0);
        logger.info("创建了一个app数据库链接");

        int day = NumberUtils.parseInt(PropertiesUtils.getProperty("/config.properties", "sync.ads.cpm"));

        Calendar c = Calendar.getInstance();
        c.add(Calendar.DAY_OF_YEAR, -day);

        String thatDay = DateUtil.getDate(DateUtil.DateFmts.YYYY_MM_DD, c.getTime());

        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");
        sql.append("    A.ID AS id, ");
        sql.append("    A.ADID AS adid, ");
        sql.append("    IFNULL(A.CPM_PRICE, 0) AS minCpm, ");
        sql.append("    IFNULL(ROUND(B.REVENUE * 1000 / B.CLICKS, 2), 0) AS cpc, ");
        sql.append("    IFNULL(ROUND(B.REVENUE * 1000 / B.IMPRESSIONS, 2), 0) AS cpm ");
        sql.append("FROM management_adx_ads A ");
        sql.append("LEFT JOIN ( ");
        sql.append("    SELECT ");
        sql.append("        ADID, ");
        sql.append("        SUM(REQUESTS) AS REQUESTS, ");
        sql.append("        SUM(PAYMENT) AS REVENUE, ");
        sql.append("        SUM(CLICKS) AS CLICKS, ");
        sql.append("        SUM(IMPRESSIONS) AS IMPRESSIONS ");
        sql.append("    FROM report_daily_adx_data ");
        sql.append("    WHERE DATE >= '" + thatDay + "' ");
        sql.append("    AND REQUESTS > 1000 ");
        sql.append("    GROUP BY ADID ");
        sql.append(") B ON A.ID = B.ADID ");

        String title = "[ADX -> ADS]";
        List<AdsCpmAndCpc> data = pull(server, sql.toString(), title);

        List<Map<String, Object>> list = mapper.getList("syncAdsSqlMapper.listAdx");

        for (Map<String, Object> map : list) {

            AdsCpmAndCpc tmp = get(data, StringUtils.toString(map.get("adid")));

            if (CheckUtils.isNull(tmp)) {
                continue;
            }

            map.put("cpm", NumberUtils.max(tmp.getCpm(), NumberUtils.parseDouble(map.get("cpm_price")), FloorPrice.CPM));
            map.put("cpc", NumberUtils.max(tmp.getCpc(), FloorPrice.CPC));
            mapper.save("syncAdsSqlMapper.updateAdx", map);
        }

        logger.info("cpm、cpc更新成功：" + title);
    }

    public void syncPc() {

        SqlServer server = new SqlServer(config.getManageHost(), NumberUtils.parseInt(config.getManagePort()), config.getManageUser(),
                config.getManagePass(), config.getManageDbname(), 0);
        logger.info("创建了一个pc数据库链接");

        int day = NumberUtils.parseInt(PropertiesUtils.getProperty("/config.properties", "sync.ads.cpm"));

        Calendar c = Calendar.getInstance();
        c.add(Calendar.DAY_OF_YEAR, -day);

        String thatDay = DateUtil.getDate(DateUtil.DateFmts.YYYY_MM_DD, c.getTime());

        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");
        sql.append("    A.ID AS id, ");
        sql.append("    A.ADENCODE AS adid, ");
        sql.append("    IFNULL(A.biddingCpm, 0) AS minCpm, ");
        sql.append("    IFNULL(ROUND(B.REVENUE * 1000 / B.CLICKS, 2), 0) AS cpc, ");
        sql.append("    IFNULL(ROUND(B.REVENUE * 1000 / B.IMPRESSIONS, 2), 0) AS cpm ");
        sql.append("FROM management_ads A ");
        sql.append("LEFT JOIN ( ");
        sql.append("    SELECT ");
        sql.append("        ADID, ");
        sql.append("        SUM(REQUESTS) AS REQUESTS, ");
        sql.append("        SUM(REVENUE) AS REVENUE, ");
        sql.append("        SUM(CLICKS) AS CLICKS, ");
        sql.append("        SUM(IMPRESSIONS) AS IMPRESSIONS ");
        sql.append("    FROM report_daily_channel_data ");
        sql.append("    WHERE DATE >= '" + thatDay + "' ");
        sql.append("    AND REQUESTS > 1000 ");
        sql.append("    GROUP BY ADID ");
        sql.append(") B ON A.ID = B.ADID; ");

        String title = "[PC -> ADS]";
        List<AdsCpmAndCpc> data = pull(server, sql.toString(), title);

        List<Map<String, Object>> list = mapper.getList("syncAdsSqlMapper.listPc");

        for (Map<String, Object> map : list) {

            AdsCpmAndCpc tmp = get(data, StringUtils.toString(map.get("adid")));

            if (CheckUtils.isNull(tmp)) {
                continue;
            }

            double clickprice = NumberUtils.parseDouble(map.get("clickprice"));

            int settlementType = NumberUtils.parseInt(map.get("settlementType"));

            if (settlementType == 1) {

                map.put("cpm", NumberUtils.max(tmp.getCpm(), clickprice, FloorPrice.CPM));
                map.put("cpc", NumberUtils.max(tmp.getCpc(), FloorPrice.CPC));
            } else if (settlementType == 2) {

                map.put("cpm", NumberUtils.max(tmp.getCpm(), FloorPrice.CPM));
                map.put("cpc", NumberUtils.max(tmp.getCpc(), clickprice, FloorPrice.CPC));
            }

            mapper.save("syncAdsSqlMapper.updatePc", map);
        }
        logger.info("cpm、cpc更新成功：" + title);
    }

    private AdsCpmAndCpc get(List<AdsCpmAndCpc> data, String adid) {

        if (CheckUtils.isEmpty(data)) {
            return null;
        }
        for (AdsCpmAndCpc tmp : data) {
            if (adid.equals(tmp.getAdid())) {
                return tmp;
            }
        }
        return null;
    }

    private List<AdsCpmAndCpc> pull(SqlServer server, String sql, String title) {

        long start = System.currentTimeMillis();
        ResultSet rs = server.select(sql);

        List<AdsCpmAndCpc> data = null;
        try {
            data = DBHelper.mapping(rs, AdsCpmAndCpc.class);
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (data == null || data.isEmpty()) {
            logger.warn("没有数据或查询失败:" + sql);

            System.out.println("没有数据或查询失败:" + sql);
        }

        logger.info("远程查询用时" + (System.currentTimeMillis() - start) + "ms, " + data.size() + "条数据 --> " + title);
        return data;
    }
}
